package com.exam.dao;

import com.exam.domain.entity.Exam;
import com.exam.domain.entity.Question;
import com.exam.domain.entity.Teacher;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TeacherDao {
    // 向题库中添加试题
    public Integer addQuestion(int teacherId, String title, String answer, String optionA, String optionB, String optionC, String optionD) {
        // SQL 插入语句
        String sql = "INSERT INTO questions (created_by, question_text, correct_option, option_a, option_b, option_c, option_d) VALUES (?, ?, ?, ?, ?, ?, ?)";

        try  {
            // 获取数据库连接
            Connection connection = DBUtil.getConnection();

            int result = DBUtil.update(connection, sql, teacherId, title, answer, optionA, optionB, optionC, optionD);

            if (result > 0) {
                return 1; // 成功插入数据，返回 1
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("添加试题失败", e);
        }

        // 插入失败，返回 0
        return 0;
    }

    public List<Question> getAllQuestionsByTeacherId(Integer teacherId) {
        String sql = "SELECT * FROM questions WHERE created_by = ?";
        // 执行查询操作，返回结果集
        // 执行查询操作，返回 ResultSet
        List<Question> questions = new ArrayList<>();
        try {
            Connection connection = DBUtil.getConnection();
            ResultSet rs = DBUtil.query(connection, sql, teacherId);
            while (rs.next()) {
                // created_by, question_text, correct_option, option_a, option_b, option_c, option_d
                // 结果集中有数据，映射到 User 对象
                Question question = new Question();
                question.setQuestionId(rs.getInt("question_id"));
                question.setQuestionText(rs.getString("question_text"));
                question.setCorrectOption(rs.getString("correct_option"));
                question.setOptionA(rs.getString("option_a"));
                question.setOptionB(rs.getString("option_b"));
                question.setOptionC(rs.getString("option_c"));
                question.setOptionD(rs.getString("option_d"));
                question.setCreatedBy(rs.getInt("created_by"));
                questions.add(question);
            }
            return questions;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("查询试题失败", e);
        }
    }


    public void createExamWithQuestions(int teacherId, String examTitle, int questionCount, int questionValue, List<Integer> questionIds) {
        Connection connection = null;
        try {
            // 获取连接并开启事务
            connection = DBUtil.getConnection();
            // 设置手动提交事务
            DBUtil.beginTransaction(connection);

            // 创建试卷
            String insertExamSql = "INSERT INTO exams (created_by, title, question_count, score_per_question) VALUES (?, ?, ?, ?)";
            DBUtil.update(connection, insertExamSql, teacherId, examTitle, questionCount, questionValue);

            // 获取刚刚创建的试卷ID（假设数据库支持返回生成的主键）
            ResultSet rs = DBUtil.query(connection, "SELECT LAST_INSERT_ID()");
            int examId = 0;
            if (rs.next()) {
                examId = rs.getInt(1);
            }
            rs.close();

            // 插入试卷题目关联
            String insertExamQuestionSql = "INSERT INTO exam_questions (exam_id, question_id) VALUES (?, ?)";
            for (int questionId : questionIds) {
                DBUtil.update(connection, insertExamQuestionSql, examId, questionId);
            }

            // 提交事务
            DBUtil.commitTransaction(connection);
            System.out.println("试卷创建成功");

        } catch (Exception e) {
            // 发生异常，回滚事务
            try {
                if (connection != null) {
                    DBUtil.rollbackTransaction(connection);
                }
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
            e.printStackTrace();
            throw new RuntimeException("创建试卷失败", e);
        } finally {
            // 关闭连接
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException closeEx) {
                closeEx.printStackTrace();
            }
        }
    }

    // 获取教师创建的试卷列表，并按照创建时间降序排列
    public List<Exam> getExamsByTeacherId(Integer teacherId) {
        String sql = "SELECT * FROM exams WHERE created_by = ? order by created_at desc";
        // 执行查询操作，返回结果集
        // 执行查询操作，返回 ResultSet
        List<Exam> exams = new ArrayList<>();
        try {
            Connection connection = DBUtil.getConnection();
            ResultSet rs = DBUtil.query(connection, sql, teacherId);
            while (rs.next()) {
                // created_by, question_text, correct_option, option_a, option_b, option_c, option_d
                // 结果集中有数据，映射到 User 对象
                Exam exam = new Exam();
                exam.setExamId(rs.getInt("exam_id"));
                exam.setCreatedBy(rs.getInt("created_by"));
                exam.setTitle(rs.getString("title"));
                exam.setQuestionCount(rs.getInt("question_count"));
                exam.setScorePerQuestion(rs.getInt("score_per_question"));
                exam.setCreatedAt(rs.getTimestamp("created_at"));
                exam.setStatus(rs.getString("status"));
                exams.add(exam);
            }
            return exams;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("查询试卷失败", e);
        }
    }

    public List<Question> getTeacherQuestionsByQuestionText(Integer teacherId, String questionText) {
        String sql = "SELECT * FROM questions WHERE created_by = ? AND question_text like ?";
        // 执行查询操作，返回结果集
        // 执行查询操作，返回 ResultSet
        List<Question> questions = new ArrayList<>();
        try {
            Connection connection = DBUtil.getConnection();
            ResultSet rs = DBUtil.query(connection, sql, teacherId, questionText);
            while (rs.next()) {
                // created_by, question_text, correct_option, option_a, option_b, option_c, option_d
                // 结果集中有数据，映射到 User 对象
                Question question = new Question();
                question.setQuestionId(rs.getInt("question_id"));
                question.setQuestionText(rs.getString("question_text"));
                question.setCorrectOption(rs.getString("correct_option"));
                question.setOptionA(rs.getString("option_a"));
                question.setOptionB(rs.getString("option_b"));
                question.setOptionC(rs.getString("option_c"));
                question.setOptionD(rs.getString("option_d"));
                question.setCreatedBy(rs.getInt("created_by"));
                questions.add(question);
            }
            return questions;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("查询试题失败", e);
        }
    }

    public Teacher getTeacherInfoById(Integer teacherId) {
        String sql = "SELECT * FROM teacher_infos WHERE teacher_id = ?";
        String examCountSql = "SELECT COUNT(*) AS examCount FROM exams WHERE created_by = ?";
        String questionCountSql = "SELECT COUNT(*) AS questionCount FROM questions WHERE created_by = ?";

        Teacher teacher = null;

        try (Connection connection = DBUtil.getConnection();
             ResultSet rs = DBUtil.query(connection, sql, teacherId)) {

            if (rs.next()) {
                // 结果集中有数据，映射到 Teacher 对象
                teacher = new Teacher();
                teacher.setTeacherId(rs.getInt("teacher_id"));
                teacher.setTeacherNo(rs.getString("teacher_no"));
                teacher.setTeacherName(rs.getString("teacher_name"));

                // 查询发布的试卷数量
                try (PreparedStatement ps = connection.prepareStatement(examCountSql)) {
                    ps.setInt(1, teacherId);
                    try (ResultSet examCountRs = ps.executeQuery()) {
                        if (examCountRs.next()) {
                            teacher.setExamCount(examCountRs.getInt("examCount"));
                        }
                    }
                }

                // 查询发布的题目数量
                try (PreparedStatement ps = connection.prepareStatement(questionCountSql)) {
                    ps.setInt(1, teacherId);
                    try (ResultSet questionCountRs = ps.executeQuery()) {
                        if (questionCountRs.next()) {
                            teacher.setQuestionCount(questionCountRs.getInt("questionCount"));
                        }
                    }
                }

                // 返回查询到的教师信息
                return teacher;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("查询教师信息失败", e);
        }
        // 如果没有找到教师信息，返回 null
        return null;
    }

}
